﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using olierFrm.DataBase.FrameWork;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
using olierFrm.DataBase.FrameWork.Plugin;


namespace olierFrm.DataBase
{
    public class SqlHelper
    {
        public SqlHelper()
        {

        }
        private SqlConnection _StaConnectoin;

        /// <summary>
        /// 获取STA库的连接
        /// </summary>
        public SqlConnection StaConnectoin
        {
            get
            {
                if (_StaConnectoin == null || _StaConnectoin.State == ConnectionState.Closed)
                {
                    _StaConnectoin = new SqlConnection(ConnectionConfig.StaConnectionStr);
                    _StaConnectoin.Open();
                }
                return _StaConnectoin;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="SQLText"></param>
        /// <param name="Params"></param>
        /// <param name="DataTableName"></param>
        /// <returns></returns>
        //public DataTable ExecuteQuery(string SQLText, List<ObjectSQLParam> Param, string DataTableName, int start = 0, int max = 0, SqlTransaction tran = null)
        //{
        //    var ds = ExecuteQuery(SQLText, Param, DataTableName, 0, 0, null);
        //    ds.Tables[0].TableName = DataTableName;
        //    return ds.Tables[0].Copy();
        //}


        /// <summary>
        /// 
        /// </summary>
        /// <param name="SQLText"></param>
        /// <param name="Param"></param>
        /// <param name="DataTableName"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string SQLText, List<ObjectSQLParam> Param, string DataTableName,
            bool IsClose = false, int startRecord = 0, int maxRecord = 0, SqlTransaction tran = null)
        {
            DataTable dt = new DataTable(DataTableName);
            DataSet ds;
            if (DataTableName != null && DataTableName.Length != 0)
                ds = new DataSet(DataTableName);
            else ds = new DataSet();
            try
            {
                //处理动态语句
                if (Param != null)
                {
                    var dynamicSQLParam = Param.Where(o => o.ParamType.ToLower() == "dynamic").ToList();
                    foreach (var p in dynamicSQLParam)
                    {
                        SQLText = SQLText.Replace(":" + p.ParamName, (p.ParamValue != null ? p.ParamValue.ToString() : ""));
                    }
                }


                SQLText = SQLText.Replace(":", "@Param_");
                SqlCommand sqlcmd = null;

                SQLText = "--password-- \r\n" + SQLText;
                if (tran == null)
                    sqlcmd = new SqlCommand(SQLText, StaConnectoin);
                else
                    sqlcmd = new SqlCommand(SQLText, StaConnectoin, tran);
                BindParam(sqlcmd, Param);
                //SqlDataAdapter sqlda = new SqlDataAdapter(sqlcmd);


                
                //var sqldr = sqlcmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
                //DataTable dtSchema = sqldr.GetSchemaTable();
                var sqldr = sqlcmd.ExecuteReader();
                if (maxRecord != 0)
                {
                    int i = 0;
                    for (; i < sqldr.FieldCount; i++)
                    {
                        var col = new DataColumn();
                        col.ColumnName = sqldr.GetName(i);
                        col.DataType = sqldr.GetFieldType(i);
                        dt.Columns.Add(col);
                    }
                    i = startRecord;
                    while (sqldr.Read())
                    {
                        if (i >= maxRecord) break;
                        var row = dt.NewRow();
                        for (int c = 0; c < sqldr.FieldCount; c++)
                        {
                            row[c] = sqldr[c];
                        }
                        dt.Rows.Add(row);
                        i++;
                    }

                    //sqlda.Fill(ds, start, max, DataTableName);
                    //dt = ds.Tables[0].Copy();
                }
                else
                    dt.Load(sqldr);
                //sqlda.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (IsClose)
                    CloseConnecton();
#if (DEBUG)
              //  olierFrm.Log.Log4.LogInfo.Info(SQLText + "\r\ninParams\t" + Param.ToJSON());
#endif

            }
            return dt.Copy();
        }


        #region 泛型实体

        private List<string> GetColumns(SqlDataReader sqlDataReader)
        {
            List<string> columns = new List<string>();
            for (int i = 0; i < sqlDataReader.FieldCount; i++)
            {
                var columnName = sqlDataReader.GetName(i).ToLower();

                if (!columns.Contains(columnName)) columns.Add(columnName);
            }

            return columns;
        }
        /*
        public List<T> ExecuteQuery<T>(string SQLText, List<ObjectSQLParam> Param) where T : class, new()
        {
            try
            {
                SQLText = SQLText.Replace(":", "@Param_");
                SqlCommand sqlcmd = new SqlCommand(SQLText, StaConnectoin);
                BindParam(sqlcmd, Param);
                SqlDataReader sqldr = sqlcmd.ExecuteReader();
                IList<T> listModels = new List<T>();

                T newEntity = new T();
                var entityProperties = newEntity.GetType().GetProperties();
                var columns = GetColumns(sqldr);
                while (sqldr.Read())
                {

                    foreach (var c in columns)
                    {
                        var entity = entityProperties.Where(p => p.Name.ToLower() == c);
                        if (entity.Any())
                        {
                            entity.First().SetValue(newEntity, sqldr[c], null);
                        }
                    }
                    listModels.Add(newEntity);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnecton();
            }
            return null;
        }
        */

        #endregion


        //public DataSet ExecuteQuery(string SQLText, List<ObjectSQLParam> Param, string DataTableName, int start, int max)
        //{
        //    return ExecuteQuery(SQLText, Param, DataTableName, start, max, null);
        //}

        //private static string ReplaceSQLParam(List<ObjectSQLParam> Param, string SQLText)
        //{
        //    foreach (var p in Param)
        //    {
        //        switch (p.ParamType.ToUpper().Trim())
        //        {
        //            case "":

        //                SQLText = SQLText.Replace(":" + p.ParamName, p.ParamValue);
        //                break;
        //        }
        //    }
        //    return SQLText;
        //}



        //internal DataSet ExecuteQuery(List<ObjectSQL> SQLText, List<ObjectSQLParam> Param)
        //{
        //    DataSet dataset = new DataSet();
        //    foreach (var s in SQLText)
        //    {
        //        try
        //        {
        //            dataset.Tables.Add(ExecuteQuery(s.SQLText, Param, s.SqlID + "_" + s.Idx, 0, 0));
        //            dataset.DataSetName = s.SqlID;
        //        }
        //        catch (Exception ex)
        //        {
        //            throw ex;
        //        }
        //        finally
        //        {
        //            CloseConnecton();
        //        }
        //    }
        //    return dataset;
        //}

        public int ExecuteQuery(string SQLText, List<ObjectSQLParam> Param, bool IsClose = false, SqlTransaction Tran = null)
        {
            List<ObjectSQLParam> outputParams = null;
            var inParams = Param.Clone();
            try
            {

                //处理动态语句
                var dynamicSQLParam = Param.Where(o => o.ParamType.ToLower() == "dynamic").ToList();
                foreach (var p in dynamicSQLParam)
                {
                    SQLText = SQLText.Replace(":" + p.ParamName, (p.ParamValue != null ? p.ParamValue.ToString() : ""));
                }


                SqlCommand sqlcmd;
                SQLText = SQLText.Replace(":", "@Param_");
                SQLText = "--password-- \r\n" + SQLText;
                if (Tran == null)
                {
                    sqlcmd = new SqlCommand(SQLText, StaConnectoin);
                }
                else
                {
                    sqlcmd = new SqlCommand(SQLText, StaConnectoin, Tran);
                }
                BindParam(sqlcmd, Param);
                int c = sqlcmd.ExecuteNonQuery();
                outputParams = Param.Where(p => p.OutPut).ToList();
                foreach (var p in outputParams)
                {
                    p.ParamValue = sqlcmd.Parameters["@Param_" + p.ParamName].Value;
                }
                return c;

            }
            catch (Exception ex)
            {
                if (Tran != null)
                    Tran.Rollback();
                CloseConnecton();
                throw ex;
            }
            finally
            {

#if (DEBUG)
                //string output = "";
                //if (outputParams != null && outputParams.Count != 0)
                //{
                //    output = "\r\noutputParams\t" + outputParams.ToJSON();
                //}
                //olierFrm.Log.Log4.LogInfo.Info(SQLText + "\r\ninParams\t" + inParams.ToJSON() + outputParams);

#endif

                if (IsClose)
                    CloseConnecton();
            }
        }

        /// <summary>
        /// 找出SQLText的所有参数
        /// </summary>
        /// <param name="sqlText">SQL</param>
        /// <param name="Param">参数</param>
        /// <returns>SQLText的参数焦合</returns>
        public List<ObjectSQLParam> FilterParamsForSqlTextByObjectSQLParam(string sqlText, List<ObjectSQLParam> Param)
        {
            var fields = sqlText.Replace("\r", " ").Replace("\n", " ").Replace(",", " ").
                   Replace("[", " ").Replace("]", " ").Replace("=", " ").Replace("(", " ").Replace(")", " ").Split(' ').ToList();
            fields.Remove(" ");
            var curParams = Param.Where(p => fields.Where(f => f.IndexOf(":" + p.ParamName) != -1).Any());
            if (curParams.Any()) return curParams.ToList();
            else return new List<ObjectSQLParam>();
        }



        public void CloseConnecton()
        {

            if (_StaConnectoin != null && _StaConnectoin.State != ConnectionState.Closed)
            {
                _StaConnectoin.Close();
            }
        }

        public void BindParam(System.Data.SqlClient.SqlCommand SqlCmd, List<ObjectSQLParam> ObjectSQLParam)
        {
            if (ObjectSQLParam != null)
                foreach (var osp in ObjectSQLParam)
                {
                    //if (osp.Repeated) continue;

                    if (osp.ParamType.IndexOf("%") == 0) osp.ParamValue = "%" + osp.ParamValue;
                    if (osp.ParamType.LastIndexOf("%") == osp.ParamType.Length - 1) osp.ParamValue += "%";
                    SqlParameter sqlp = new SqlParameter();
                    sqlp.ParameterName = "@Param_" + osp.ParamName;
                    sqlp.SqlDbType = SqlGetParamType(osp.ParamType);
                    sqlp.IsNullable = true;
                    sqlp.SourceColumnNullMapping = true;
                    if (sqlp.SqlDbType.ToString().ToLower() == "int" && (osp.ParamValue == null || osp.ParamValue.ToString().Length == 0))
                    {
                        sqlp.Value = DBNull.Value;
                    }
                    else if (sqlp.SqlDbType.ToString().ToLower() == "bit" && (osp.ParamValue == null || osp.ParamValue.ToString() == ""))
                    {
                        sqlp.Value = DBNull.Value;
                    }
                    else if (sqlp.SqlDbType.ToString().ToLower() == "datetime" && (osp.ParamValue == null || osp.ParamValue.ToString() == ""))
                    {
                        sqlp.Value = DBNull.Value;
                    }
                    else if (osp.ParamType.ToLower() == "dynamic")
                    {
                        continue;
                    }
                    else
                        sqlp.Value = osp.ParamValue == null ? DBNull.Value : osp.ParamValue;

                    if (osp.OutPut)
                    {
                        osp.ParamValue = sqlp.Value;
                        sqlp.Direction = ParameterDirection.InputOutput;
                        sqlp.Size = 102400;
                    }
                    SqlCmd.Parameters.Add(sqlp);
                }
        }

        public System.Data.SqlDbType SqlGetParamType(string strParamType)
        {
            if (strParamType.ToString().ToUpper() == "INTEGER")
                return System.Data.SqlDbType.Int;
            else if (strParamType.ToString().ToUpper() == "MONTH")
                return System.Data.SqlDbType.Int;
            else if (strParamType.ToString().ToUpper() == "FLOAT")
                return System.Data.SqlDbType.Float;


            else if (strParamType.ToString().ToUpper().IndexOf("DATE") != -1)
                return System.Data.SqlDbType.DateTime;
            //为了兼容SQL2K
            //else if (strParamType.ToString().ToUpper() == "DATE")
            //    return System.Data.SqlDbType.Date;
            //else if (strParamType.ToString().ToUpper() == "ENDDATE")
            //    return System.Data.SqlDbType.Date;
            //else if (strParamType.ToString().ToUpper() == "DATETIME")
            //    return System.Data.SqlDbType.DateTime;
            //else if (strParamType.ToString().ToUpper() == "TIME")
            //    return System.Data.SqlDbType.DateTime;

            else if (strParamType.ToString().ToUpper() == "BOOLEAN")
                return System.Data.SqlDbType.Bit;
            else if (strParamType.ToString().ToUpper() == "MEMO")
                return System.Data.SqlDbType.Text;
            else if (strParamType.ToString().ToUpper() == "BLOB")
                return System.Data.SqlDbType.Binary;
            else
                return System.Data.SqlDbType.VarChar;
        }
    }

}
